#include "databasemanager.h"
#include <QDir>
#include <QDebug>
#include <QDateTime>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>

// 单例实例指针初始化
DatabaseManager* DatabaseManager::m_instance = nullptr;

// 获取DatabaseManager单例实例
DatabaseManager& DatabaseManager::getInstance()
{
    if (m_instance == nullptr) {
        m_instance = new DatabaseManager();
    }
    return *m_instance;
}

// 构造函数
DatabaseManager::DatabaseManager(QObject *parent) : QObject(parent)
{
    m_database = QSqlDatabase::addDatabase("QSQLITE");
    m_database.setDatabaseName("warehouse.db");
}

// 析构函数
DatabaseManager::~DatabaseManager()
{
    closeDatabase();
}

// 初始化数据库（连接、创建表、插入默认用户）
bool DatabaseManager::initializeDatabase()
{
    if (!connectToDatabase()) {
        return false;
    }
    
    if (!createTables()) {
        return false;
    }
    
    if (!migrateDatabase()) {
        return false;
    }
    
    if (!insertDefaultAdmin()) {
        return false;
    }
    
    return true;
}

// 连接到数据库
bool DatabaseManager::connectToDatabase()
{
    if (!m_database.open()) {
        qDebug() << "数据库连接失败:" << m_database.lastError().text();
        return false;
    }
    qDebug() << "数据库连接成功";
    return true;
}

// 关闭数据库连接
void DatabaseManager::closeDatabase()
{
    if (m_database.isOpen()) {
        m_database.close();
    }
}

// 获取数据库对象
QSqlDatabase DatabaseManager::getDatabase()
{
    return m_database;
}

// 创建数据库表
bool DatabaseManager::createTables()
{
    QSqlQuery query(m_database);
    
    // 创建users表
    QString createUsersTable = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL CHECK(role IN ('admin', 'user')),
            created_at DATETIME NOT NULL
        )
    )";
    
    if (!query.exec(createUsersTable)) {
        qDebug() << "创建users表失败:" << query.lastError().text();
        return false;
    }
    
    // 创建items表
    QString createItemsTable = R"(
        CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            model TEXT,
            quantity INTEGER NOT NULL DEFAULT 0,
            location TEXT
        )
    )";
    
    if (!query.exec(createItemsTable)) {
        qDebug() << "创建items表失败:" << query.lastError().text();
        return false;
    }
    
    // 创建records表
    QString createRecordsTable = R"(
        CREATE TABLE IF NOT EXISTS records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            item_id INTEGER NOT NULL,
            type TEXT NOT NULL CHECK(type IN ('入库', '出库')),
            quantity INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(item_id) REFERENCES items(id),
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
    )";
    
    if (!query.exec(createRecordsTable)) {
        qDebug() << "创建records表失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "所有表创建成功";
    return true;
}

// 数据库迁移
bool DatabaseManager::migrateDatabase()
{
    QSqlQuery query(m_database);
    
    // 检查users表是否已有created_at字段
    if (!query.exec("PRAGMA table_info(users)")) {
        qDebug() << "检查表结构失败:" << query.lastError().text();
        return false;
    }
    
    bool hasCreatedAt = false;
    while (query.next()) {
        if (query.value(1).toString() == "created_at") {
            hasCreatedAt = true;
            break;
        }
    }
    
    // 如果没有created_at字段，则添加
    if (!hasCreatedAt) {
        qDebug() << "添加created_at字段到users表";
        if (!query.exec("ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP")) {
            qDebug() << "添加created_at字段失败:" << query.lastError().text();
            return false;
        }
        
        // 为现有用户更新创建时间
        QString currentTime = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
        query.prepare("UPDATE users SET created_at = ? WHERE created_at IS NULL OR created_at = ''");
        query.addBindValue(currentTime);
        if (!query.exec()) {
            qDebug() << "更新现有用户创建时间失败:" << query.lastError().text();
            return false;
        }
        
        qDebug() << "数据库迁移完成";
    } else {
        qDebug() << "数据库已是最新版本";
    }
    
    return true;
}

// 插入默认管理员用户
bool DatabaseManager::insertDefaultAdmin()
{
    QSqlQuery query(m_database);
    
    // 检查是否已存在admin用户
    query.prepare("SELECT COUNT(*) FROM users WHERE username = ?");
    query.addBindValue("admin");
    
    if (!query.exec()) {
        qDebug() << "检查admin用户失败:" << query.lastError().text();
        return false;
    }
    
    query.next();
    if (query.value(0).toInt() > 0) {
        qDebug() << "admin用户已存在";
        return true;
    }
    
    // 插入默认管理员账户（明文密码）
    QString currentTime = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    query.prepare("INSERT INTO users (username, password, role, created_at) VALUES (?, ?, ?, ?)");
    query.addBindValue("admin");
    query.addBindValue("admin123");  // 明文存储
    query.addBindValue("admin");
    query.addBindValue(currentTime);
    
    if (!query.exec()) {
        qDebug() << "插入默认admin用户失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "默认admin用户创建成功";
    return true;
}

// 验证用户登录
bool DatabaseManager::validateUser(const QString& username, const QString& password, UserInfo& userInfo)
{
    QSqlQuery query(m_database);
    
    query.prepare("SELECT id, username, role, created_at FROM users WHERE username = ? AND password = ?");
    query.addBindValue(username);
    query.addBindValue(password);
    
    if (!query.exec()) {
        qDebug() << "验证用户失败:" << query.lastError().text();
        return false;
    }
    
    if (query.next()) {
        userInfo.id = query.value(0).toInt();
        userInfo.username = query.value(1).toString();
        userInfo.role = query.value(2).toString();
        userInfo.createdAt = query.value(3).toString();
        return true;
    }
    
    return false;
}

// 注册新用户（固定为普通用户）
bool DatabaseManager::registerUser(const QString& username, const QString& password)
{
    QSqlQuery query(m_database);
    
    QString currentTime = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    query.prepare("INSERT INTO users (username, password, role, created_at) VALUES (?, ?, ?, ?)");
    query.addBindValue(username);
    query.addBindValue(password);
    query.addBindValue("user");  // 固定为普通用户
    query.addBindValue(currentTime);
    
    if (!query.exec()) {
        qDebug() << "注册用户失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "用户注册成功:" << username;
    return true;
}

// 检查用户名是否已存在
bool DatabaseManager::isUsernameExists(const QString& username)
{
    QSqlQuery query(m_database);
    
    query.prepare("SELECT COUNT(*) FROM users WHERE username = ?");
    query.addBindValue(username);
    
    if (!query.exec()) {
        qDebug() << "检查用户名失败:" << query.lastError().text();
        return false;
    }
    
    query.next();
    return query.value(0).toInt() > 0;
}

// 获取所有用户列表
QList<UserInfo> DatabaseManager::getAllUsers()
{
    QList<UserInfo> users;
    QSqlQuery query(m_database);
    
    if (!query.exec("SELECT id, username, role, created_at FROM users ORDER BY id")) {
        qDebug() << "获取用户列表失败:" << query.lastError().text();
        return users;
    }
    
    while (query.next()) {
        UserInfo user;
        user.id = query.value(0).toInt();
        user.username = query.value(1).toString();
        user.role = query.value(2).toString();
        user.createdAt = query.value(3).toString();
        users.append(user);
    }
    
    return users;
}

// 添加新用户（管理员可创建任意角色）
bool DatabaseManager::addUser(const QString& username, const QString& password, const QString& role)
{
    QSqlQuery query(m_database);
    
    QString currentTime = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    query.prepare("INSERT INTO users (username, password, role, created_at) VALUES (?, ?, ?, ?)");
    query.addBindValue(username);
    query.addBindValue(password);
    query.addBindValue(role);
    query.addBindValue(currentTime);
    
    if (!query.exec()) {
        qDebug() << "添加用户失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "用户添加成功:" << username << "角色:" << role;
    return true;
}

// 更新用户信息
bool DatabaseManager::updateUser(int userId, const QString& username, const QString& password, const QString& role)
{
    QSqlQuery query(m_database);
    
    // 如果密码为空，则不更新密码
    if (password.isEmpty()) {
        query.prepare("UPDATE users SET username = ?, role = ? WHERE id = ?");
        query.addBindValue(username);
        query.addBindValue(role);
        query.addBindValue(userId);
    } else {
        query.prepare("UPDATE users SET username = ?, password = ?, role = ? WHERE id = ?");
        query.addBindValue(username);
        query.addBindValue(password);
        query.addBindValue(role);
        query.addBindValue(userId);
    }
    
    if (!query.exec()) {
        qDebug() << "更新用户失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "用户更新成功:" << username;
    return true;
}

// 删除用户
bool DatabaseManager::deleteUser(int userId)
{
    QSqlQuery query(m_database);
    
    query.prepare("DELETE FROM users WHERE id = ?");
    query.addBindValue(userId);
    
    if (!query.exec()) {
        qDebug() << "删除用户失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "用户删除成功，ID:" << userId;
    return true;
}

// 根据用户名搜索用户
QList<UserInfo> DatabaseManager::searchUsers(const QString& searchText)
{
    QList<UserInfo> users;
    QSqlQuery query(m_database);
    
    query.prepare("SELECT id, username, role, created_at FROM users WHERE username LIKE ? ORDER BY id");
    query.addBindValue(QString("%%1%").arg(searchText));
    
    if (!query.exec()) {
        qDebug() << "搜索用户失败:" << query.lastError().text();
        return users;
    }
    
    while (query.next()) {
        UserInfo user;
        user.id = query.value(0).toInt();
        user.username = query.value(1).toString();
        user.role = query.value(2).toString();
        user.createdAt = query.value(3).toString();
        users.append(user);
    }
    
    return users;
}

// === 物品管理方法实现 ===

// 获取所有物品列表
QList<ItemInfo> DatabaseManager::getAllItems()
{
    QList<ItemInfo> items;
    QSqlQuery query(m_database);
    
    if (!query.exec("SELECT id, name, model, quantity, location FROM items ORDER BY name")) {
        qDebug() << "获取物品列表失败:" << query.lastError().text();
        return items;
    }
    
    while (query.next()) {
        ItemInfo item;
        item.id = query.value(0).toInt();
        item.name = query.value(1).toString();
        item.model = query.value(2).toString();
        item.quantity = query.value(3).toInt();
        item.location = query.value(4).toString();
        items.append(item);
    }
    
    return items;
}

// 添加新物品
bool DatabaseManager::addItem(const QString& name, const QString& model, int quantity, const QString& location)
{
    QSqlQuery query(m_database);
    
    query.prepare("INSERT INTO items (name, model, quantity, location) VALUES (?, ?, ?, ?)");
    query.addBindValue(name);
    query.addBindValue(model);
    query.addBindValue(quantity);
    query.addBindValue(location);
    
    if (!query.exec()) {
        qDebug() << "添加物品失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "物品添加成功:" << name;
    return true;
}

// 更新物品信息
bool DatabaseManager::updateItem(int itemId, const QString& name, const QString& model, int quantity, const QString& location)
{
    QSqlQuery query(m_database);
    
    query.prepare("UPDATE items SET name = ?, model = ?, quantity = ?, location = ? WHERE id = ?");
    query.addBindValue(name);
    query.addBindValue(model);
    query.addBindValue(quantity);
    query.addBindValue(location);
    query.addBindValue(itemId);
    
    if (!query.exec()) {
        qDebug() << "更新物品失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "物品更新成功:" << name;
    return true;
}

// 删除物品
bool DatabaseManager::deleteItem(int itemId)
{
    QSqlQuery query(m_database);
    
    // 首先删除相关的库存记录
    query.prepare("DELETE FROM records WHERE item_id = ?");
    query.addBindValue(itemId);
    
    if (!query.exec()) {
        qDebug() << "删除物品相关记录失败:" << query.lastError().text();
        return false;
    }
    
    // 然后删除物品本身
    query.prepare("DELETE FROM items WHERE id = ?");
    query.addBindValue(itemId);
    
    if (!query.exec()) {
        qDebug() << "删除物品失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "物品删除成功，ID:" << itemId;
    return true;
}

// 根据名称或型号搜索物品
QList<ItemInfo> DatabaseManager::searchItems(const QString& searchText)
{
    QList<ItemInfo> items;
    QSqlQuery query(m_database);
    
    query.prepare("SELECT id, name, model, quantity, location FROM items WHERE name LIKE ? OR model LIKE ? ORDER BY name");
    QString searchPattern = QString("%%1%").arg(searchText);
    query.addBindValue(searchPattern);
    query.addBindValue(searchPattern);
    
    if (!query.exec()) {
        qDebug() << "搜索物品失败:" << query.lastError().text();
        return items;
    }
    
    while (query.next()) {
        ItemInfo item;
        item.id = query.value(0).toInt();
        item.name = query.value(1).toString();
        item.model = query.value(2).toString();
        item.quantity = query.value(3).toInt();
        item.location = query.value(4).toString();
        items.append(item);
    }
    
    return items;
}

// 根据ID获取物品信息
ItemInfo DatabaseManager::getItemById(int itemId)
{
    ItemInfo item;
    QSqlQuery query(m_database);
    
    query.prepare("SELECT id, name, model, quantity, location FROM items WHERE id = ?");
    query.addBindValue(itemId);
    
    if (!query.exec()) {
        qDebug() << "获取物品信息失败:" << query.lastError().text();
        return item;
    }
    
    if (query.next()) {
        item.id = query.value(0).toInt();
        item.name = query.value(1).toString();
        item.model = query.value(2).toString();
        item.quantity = query.value(3).toInt();
        item.location = query.value(4).toString();
    }
    
    return item;
}

// === 库存操作方法实现 ===

// 添加库存操作记录（入库/出库）
bool DatabaseManager::addStockRecord(int itemId, const QString& type, int quantity, int userId)
{
    QSqlQuery query(m_database);
    
    // 显式设置当前本地时间
    QString currentTime = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    query.prepare("INSERT INTO records (item_id, type, quantity, user_id, timestamp) VALUES (?, ?, ?, ?, ?)");
    query.addBindValue(itemId);
    query.addBindValue(type);
    query.addBindValue(quantity);
    query.addBindValue(userId);
    query.addBindValue(currentTime);
    
    if (!query.exec()) {
        qDebug() << "添加库存记录失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "库存记录添加成功:" << type << "数量:" << quantity << "时间:" << currentTime;
    return true;
}

// 更新物品库存数量
bool DatabaseManager::updateItemQuantity(int itemId, int quantityChange)
{
    QSqlQuery query(m_database);
    
    query.prepare("UPDATE items SET quantity = quantity + ? WHERE id = ?");
    query.addBindValue(quantityChange);
    query.addBindValue(itemId);
    
    if (!query.exec()) {
        qDebug() << "更新库存数量失败:" << query.lastError().text();
        return false;
    }
    
    qDebug() << "库存数量更新成功，变化:" << quantityChange;
    return true;
}

// 检查库存是否充足（出库前检查）
bool DatabaseManager::checkStockAvailable(int itemId, int requiredQuantity)
{
    QSqlQuery query(m_database);
    
    query.prepare("SELECT quantity FROM items WHERE id = ?");
    query.addBindValue(itemId);
    
    if (!query.exec()) {
        qDebug() << "检查库存失败:" << query.lastError().text();
        return false;
    }
    
    if (query.next()) {
        int currentQuantity = query.value(0).toInt();
        return currentQuantity >= requiredQuantity;
    }
    
    return false;
}

// 获取库存操作记录
QList<StockRecord> DatabaseManager::getStockRecords(int itemId)
{
    QList<StockRecord> records;
    QSqlQuery query(m_database);
    
    QString sql = R"(
        SELECT r.id, r.item_id, i.name, r.type, r.quantity, u.username, r.timestamp 
        FROM records r 
        JOIN items i ON r.item_id = i.id 
        JOIN users u ON r.user_id = u.id
    )";
    
    if (itemId > 0) {
        sql += " WHERE r.item_id = ?";
        query.prepare(sql + " ORDER BY r.timestamp DESC");
        query.addBindValue(itemId);
    } else {
        query.prepare(sql + " ORDER BY r.timestamp DESC");
    }
    
    if (!query.exec()) {
        qDebug() << "获取库存记录失败:" << query.lastError().text();
        return records;
    }
    
    while (query.next()) {
        StockRecord record;
        record.id = query.value(0).toInt();
        record.itemId = query.value(1).toInt();
        record.itemName = query.value(2).toString();
        record.type = query.value(3).toString();
        record.quantity = query.value(4).toInt();
        record.username = query.value(5).toString();
        record.timestamp = query.value(6).toString();
        records.append(record);
    }
    
    return records;
}

// 获取库存统计信息
QVariantMap DatabaseManager::getInventoryStatistics()
{
    QVariantMap stats;
    QSqlQuery query(m_database);
    
    // 获取物品总数
    if (query.exec("SELECT COUNT(*) FROM items")) {
        if (query.next()) {
            stats["totalItems"] = query.value(0).toInt();
        }
    }
    
    // 获取库存总量
    if (query.exec("SELECT SUM(quantity) FROM items")) {
        if (query.next()) {
            stats["totalQuantity"] = query.value(0).toInt();
        }
    }
    
    // 获取低库存物品数量（库存小于10的物品）
    if (query.exec("SELECT COUNT(*) FROM items WHERE quantity < 10")) {
        if (query.next()) {
            stats["lowStockItems"] = query.value(0).toInt();
        }
    }
    
    // 获取最近一周的操作记录数量
    if (query.exec("SELECT COUNT(*) FROM records WHERE timestamp >= datetime('now', '-7 days')")) {
        if (query.next()) {
            stats["recentOperations"] = query.value(0).toInt();
        }
    }
    
    return stats;
}

// === 记录查询方法实现 ===

// 根据筛选条件获取记录
QList<StockRecord> DatabaseManager::getFilteredRecords(const RecordFilter& filter)
{
    QList<StockRecord> records;
    QSqlQuery query(m_database);
    
    QString sql = R"(
        SELECT r.id, r.item_id, i.name, r.type, r.quantity, u.username, r.timestamp 
        FROM records r 
        JOIN items i ON r.item_id = i.id 
        JOIN users u ON r.user_id = u.id
        WHERE 1=1
    )";
    
    QStringList conditions;
    QList<QVariant> values;
    
    // 添加日期范围条件
    if (!filter.startDate.isEmpty()) {
        conditions << "DATE(r.timestamp) >= ?";
        values << filter.startDate;
    }
    if (!filter.endDate.isEmpty()) {
        conditions << "DATE(r.timestamp) <= ?";
        values << filter.endDate;
    }
    
    // 添加用户条件
    if (filter.userId > 0) {
        conditions << "r.user_id = ?";
        values << filter.userId;
    }
    
    // 添加物品条件
    if (filter.itemId > 0) {
        conditions << "r.item_id = ?";
        values << filter.itemId;
    }
    
    // 添加操作类型条件
    if (!filter.type.isEmpty()) {
        conditions << "r.type = ?";
        values << filter.type;
    }
    
    // 组合条件
    if (!conditions.isEmpty()) {
        sql += " AND " + conditions.join(" AND ");
    }
    
    sql += " ORDER BY r.timestamp DESC";
    
    query.prepare(sql);
    for (const QVariant& value : values) {
        query.addBindValue(value);
    }
    
    if (!query.exec()) {
        qDebug() << "获取筛选记录失败:" << query.lastError().text();
        return records;
    }
    
    while (query.next()) {
        StockRecord record;
        record.id = query.value(0).toInt();
        record.itemId = query.value(1).toInt();
        record.itemName = query.value(2).toString();
        record.type = query.value(3).toString();
        record.quantity = query.value(4).toInt();
        record.username = query.value(5).toString();
        record.timestamp = query.value(6).toString();
        records.append(record);
    }
    
    return records;
}

// 获取指定用户的记录
QList<StockRecord> DatabaseManager::getUserRecords(int userId)
{
    RecordFilter filter;
    filter.userId = userId;
    return getFilteredRecords(filter);
}

// 获取指定物品的记录
QList<StockRecord> DatabaseManager::getItemRecords(int itemId)
{
    RecordFilter filter;
    filter.itemId = itemId;
    return getFilteredRecords(filter);
}

// 获取日期范围内的记录
QList<StockRecord> DatabaseManager::getRecordsByDateRange(const QString& startDate, const QString& endDate)
{
    RecordFilter filter;
    filter.startDate = startDate;
    filter.endDate = endDate;
    return getFilteredRecords(filter);
}

// 获取记录总数
int DatabaseManager::getTotalRecordsCount()
{
    QSqlQuery query(m_database);
    
    if (query.exec("SELECT COUNT(*) FROM records")) {
        if (query.next()) {
            return query.value(0).toInt();
        }
    }
    
    return 0;
}

// 获取记录统计信息
QVariantMap DatabaseManager::getRecordsStatistics()
{
    QVariantMap stats;
    QSqlQuery query(m_database);
    
    // 获取记录总数
    stats["totalRecords"] = getTotalRecordsCount();
    
    // 获取本月操作数量
    if (query.exec("SELECT COUNT(*) FROM records WHERE DATE(timestamp) >= DATE('now', 'start of month')")) {
        if (query.next()) {
            stats["monthlyRecords"] = query.value(0).toInt();
        }
    }
    
    // 获取本月入库数量
    if (query.exec("SELECT COUNT(*) FROM records WHERE type = '入库' AND DATE(timestamp) >= DATE('now', 'start of month')")) {
        if (query.next()) {
            stats["monthlyStockIn"] = query.value(0).toInt();
        }
    }
    
    // 获取本月出库数量
    if (query.exec("SELECT COUNT(*) FROM records WHERE type = '出库' AND DATE(timestamp) >= DATE('now', 'start of month')")) {
        if (query.next()) {
            stats["monthlyStockOut"] = query.value(0).toInt();
        }
    }
    
    // 获取今日操作数量
    if (query.exec("SELECT COUNT(*) FROM records WHERE DATE(timestamp) = DATE('now')")) {
        if (query.next()) {
            stats["todayRecords"] = query.value(0).toInt();
        }
    }
    
    // 获取最近活跃用户数量
    if (query.exec("SELECT COUNT(DISTINCT user_id) FROM records WHERE timestamp >= datetime('now', '-7 days')")) {
        if (query.next()) {
            stats["activeUsers"] = query.value(0).toInt();
        }
    }
    
    return stats;
}

// === 统计报表方法实现 ===

// 获取详细库存统计
InventoryStatistics DatabaseManager::getDetailedInventoryStatistics()
{
    InventoryStatistics stats;
    QSqlQuery query(m_database);
    
    // 获取基础统计数据
    if (query.exec("SELECT COUNT(*), SUM(quantity), AVG(quantity) FROM items")) {
        if (query.next()) {
            stats.totalItems = query.value(0).toInt();
            stats.totalQuantity = query.value(1).toInt();
            stats.averageQuantity = query.value(2).toDouble();
        }
    }
    
    // 获取低库存物品数量
    if (query.exec("SELECT COUNT(*) FROM items WHERE quantity < 10")) {
        if (query.next()) {
            stats.lowStockItems = query.value(0).toInt();
        }
    }
    
    // 获取库存最多的前5个物品
    if (query.exec("SELECT name, quantity FROM items ORDER BY quantity DESC LIMIT 5")) {
        while (query.next()) {
            stats.topItems.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    // 获取低库存物品详情
    if (query.exec("SELECT name, quantity FROM items WHERE quantity < 10 ORDER BY quantity ASC LIMIT 10")) {
        while (query.next()) {
            stats.lowStockList.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    return stats;
}

// 获取操作统计（指定时间范围）
OperationStatistics DatabaseManager::getDetailedOperationStatistics(const QString& startDate, const QString& endDate)
{
    OperationStatistics stats;
    // 初始化所有统计字段为0，避免未初始化导致的异常数值
    stats.stockInCount = 0;
    stats.stockOutCount = 0;
    stats.totalOperations = 0;
    
    QSqlQuery query(m_database);
    
    QString whereClause;
    if (!startDate.isEmpty() && !endDate.isEmpty()) {
        whereClause = QString(" WHERE DATE(timestamp) >= '%1' AND DATE(timestamp) <= '%2'").arg(startDate, endDate);
    }
    
    // 获取操作总数
    QString totalSql = "SELECT COUNT(*) FROM records" + whereClause;
    if (query.exec(totalSql)) {
        if (query.next()) {
            stats.totalOperations = query.value(0).toInt();
        }
    }
    
    // 获取入库和出库统计
    QString typeSql = "SELECT type, COUNT(*) FROM records" + whereClause + " GROUP BY type";
    if (query.exec(typeSql)) {
        while (query.next()) {
            QString type = query.value(0).toString();
            int count = query.value(1).toInt();
            
            qDebug() << "操作类型:" << type << "数量:" << count;
            
            if (type == "入库") {
                stats.stockInCount = count;
            } else if (type == "出库") {
                stats.stockOutCount = count;
                // 如果出库次数异常大，检查是否有重复数据
                if (count > 10000) {
                    qDebug() << "警告：出库次数异常大" << count;
                }
            }
        }
    }
    
    // 获取用户活跃度（前10名）
    QString userSql = R"(
        SELECT u.username, COUNT(r.id) as count 
        FROM records r 
        JOIN users u ON r.user_id = u.id
    )" + whereClause + " GROUP BY u.username ORDER BY count DESC LIMIT 10";
    
    if (query.exec(userSql)) {
        while (query.next()) {
            stats.userActivity.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    // 获取月度操作统计（最近12个月）
    QString monthlySql = R"(
        SELECT strftime('%Y-%m', timestamp) as month, COUNT(*) as count
        FROM records
        WHERE timestamp >= datetime('now', '-12 months')
        GROUP BY month
        ORDER BY month
    )";
    
    if (query.exec(monthlySql)) {
        while (query.next()) {
            stats.monthlyOperations.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    // 获取物品操作频率（前10名）
    QString itemSql = R"(
        SELECT i.name, COUNT(r.id) as count
        FROM records r
        JOIN items i ON r.item_id = i.id
    )" + whereClause + " GROUP BY i.name ORDER BY count DESC LIMIT 10";
    
    if (query.exec(itemSql)) {
        while (query.next()) {
            stats.itemActivity.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    return stats;
}

// 获取趋势数据（指定月份数）
TrendData DatabaseManager::getTrendAnalysisData(int months)
{
    TrendData data;
    QSqlQuery query(m_database);
    
    // 获取月度入库趋势
    QString stockInSql = QString(R"(
        SELECT strftime('%Y-%m', timestamp) as month, COUNT(*) as count
        FROM records
        WHERE type = '入库' AND timestamp >= datetime('now', '-%1 months')
        GROUP BY month
        ORDER BY month
    )").arg(months);
    
    if (query.exec(stockInSql)) {
        while (query.next()) {
            data.monthlyStockIn.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    // 获取月度出库趋势
    QString stockOutSql = QString(R"(
        SELECT strftime('%Y-%m', timestamp) as month, COUNT(*) as count
        FROM records
        WHERE type = '出库' AND timestamp >= datetime('now', '-%1 months')
        GROUP BY month
        ORDER BY month
    )").arg(months);
    
    if (query.exec(stockOutSql)) {
        while (query.next()) {
            data.monthlyStockOut.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    // 获取库存变化趋势（模拟数据，实际应该基于历史库存快照）
    QString inventorySql = QString(R"(
        SELECT strftime('%%Y-%%m', timestamp) as month, 
               SUM(CASE WHEN type = '入库' THEN quantity ELSE -quantity END) as net_change
        FROM records
        WHERE timestamp >= datetime('now', '-%1 months')
        GROUP BY month
        ORDER BY month
    )").arg(months);
    
    if (query.exec(inventorySql)) {
        while (query.next()) {
            data.inventoryTrend.append(qMakePair(query.value(0).toString(), query.value(1).toInt()));
        }
    }
    
    return data;
}

// 获取物品价值统计（假设每个物品有基础价值）
QList<QPair<QString, double>> DatabaseManager::getItemValueStatistics()
{
    QList<QPair<QString, double>> valueStats;
    QSqlQuery query(m_database);
    
    // 简化实现：假设每个物品价值为10元，实际项目中应该在items表中添加price字段
    if (query.exec("SELECT name, quantity FROM items ORDER BY quantity DESC LIMIT 10")) {
        while (query.next()) {
            QString name = query.value(0).toString();
            int quantity = query.value(1).toInt();
            double value = quantity * 10.0; // 假设单价10元
            valueStats.append(qMakePair(name, value));
        }
    }
    
    return valueStats;
}

// 获取仪表板汇总数据
QVariantMap DatabaseManager::getDashboardSummary()
{
    QVariantMap summary;
    QSqlQuery query(m_database);
    
    // 获取基础统计
    if (query.exec("SELECT COUNT(*), SUM(quantity) FROM items")) {
        if (query.next()) {
            summary["totalItems"] = query.value(0).toInt();
            summary["totalQuantity"] = query.value(1).toInt();
        }
    }
    
    // 获取本月操作统计
    if (query.exec("SELECT COUNT(*) FROM records WHERE DATE(timestamp) >= DATE('now', 'start of month')")) {
        if (query.next()) {
            summary["monthlyOperations"] = query.value(0).toInt();
        }
    }
    
    // 获取活跃用户数
    if (query.exec("SELECT COUNT(DISTINCT user_id) FROM records WHERE timestamp >= datetime('now', '-7 days')")) {
        if (query.next()) {
            summary["activeUsers"] = query.value(0).toInt();
        }
    }
    
    // 获取低库存预警数
    if (query.exec("SELECT COUNT(*) FROM items WHERE quantity < 10")) {
        if (query.next()) {
            summary["lowStockAlerts"] = query.value(0).toInt();
        }
    }
    
    // 获取今日操作数
    if (query.exec("SELECT COUNT(*) FROM records WHERE DATE(timestamp) = DATE('now')")) {
        if (query.next()) {
            summary["todayOperations"] = query.value(0).toInt();
        }
    }
    
    return summary;
}